/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean_Admin_Product;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author KaiLee
 */
public class connectSQL {

    private Connection conn;

    public connectSQL() {
        conn = getConnection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/banhang?useUnicode=true&characterEncoding=UTF-8", "root", "");
    }

    public Connection getConnection(String dbClass, String dbUrl, String user, String pass) {
        Connection con = null;
        try {
            Class.forName(dbClass);
            con = DriverManager.getConnection(dbUrl, user, pass);

        } catch (Exception ex) {
        }
        return con;
    }

    public boolean checkAdmin(AdminBean admin) {

        try {
            String query = "select * from user_ad where username = ? and password = ?";

            PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query);
            ps.setString(1, admin.getUserId());
            ps.setString(2, admin.getPassword());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(connectSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public ArrayList<ProductBean> getProduct() {
        ArrayList<ProductBean> list = new ArrayList<ProductBean>();

        try {
            String query = "select * from product";
            Statement st = (Statement) conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                list.add(new ProductBean(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getString(9)));
            }
        } catch (Exception ex) {
        }
        return list;
    }

    public int themSanpham(ProductBean pro) {
        try {
            String query = "insert into product values (?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = (PreparedStatement) conn.prepareCall(query);
            ps.setInt(1, pro.getId());
            ps.setString(2, pro.getName());
            ps.setString(3, pro.getType());
            ps.setString(4, pro.getProvider());
            ps.setInt(5, pro.getPrice());
            ps.setString(6, pro.getImage());
            ps.setString(7, pro.getDetail());
            ps.setInt(8, pro.getQuantity());
            ps.setString(9, pro.getState());
            return ps.executeUpdate();
        } catch (Exception ex) {
        }
        return -1;
    }

    public int deleteProduct(String id) {
        try {
            String query = "delete from product where id = ?";
            PreparedStatement ps = (PreparedStatement) conn.prepareCall(query);
            ps.setString(1, id);
            return ps.executeUpdate();
        } catch (Exception ex) {
            System.out.print("loi");
        }
        return -1;
    }

    public ProductBean getProductByID(String id) {
        ProductBean pro = null;
        try {
            String query = "select * from product where id = ?";
            PreparedStatement ps = (PreparedStatement) conn.prepareCall(query);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                pro = new ProductBean(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getString(9));
            }
        } catch (Exception ex) {
        }
        return pro;
    }

    public int updateProduct(ProductBean pro) {
        try {
            System.out.println(pro.getId() + " " + pro.getName());
            String query = "update product set name=?, type=?, provider=?, price=?, image=?, detail=?, quantity=?, state=? where id = ?";
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query);

            ps.setString(1, pro.getName());
            ps.setString(2, pro.getType());
            ps.setString(3, pro.getProvider());
            ps.setInt(4, pro.getPrice());
            ps.setString(5, pro.getImage());
            ps.setString(6, pro.getDetail());
            ps.setInt(7, pro.getQuantity());
            ps.setString(8, pro.getState());
            ps.setInt(9, pro.getId());
            return ps.executeUpdate();
        } catch (Exception ex) {
        }
        return -1;
    }

    public int soTrang(int sodong) {
        ArrayList<ProductBean> list = getProduct();
        if (list.size() % sodong == 0) {
            return list.size() / sodong;
        }
        return list.size() / sodong + 1;
    }
    
    public ArrayList<ProductBean> getProductInPage(int page, int sodong){
        ArrayList<ProductBean> list = getProduct();
        ArrayList<ProductBean> listsp = new ArrayList<ProductBean>();
        for(int i = (page-1)*sodong; i<(page-1)*sodong + sodong;i++){
            if(i<list.size()){
                listsp.add(list.get(i));
            }
        }
       
        return listsp;
    }
    
    public boolean checkId(String id){
        try
        {
            String query = "select * from product where id = ?";
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return false;
            }
        }catch(Exception ex){}
        return true;
    }
}
